
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 12/09/2012 15:52:03
-- Generated from EDMX file: C:\Users\Luka\Documents\Visual Studio 2012\Projects\MSacademic\tagsubsection\MSAcademic.v2.Database\MSAcademic.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [MSAcademic.v2];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_AS_User_PasswordResetToken]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PasswordResetTokens] DROP CONSTRAINT [FK_AS_User_PasswordResetToken];
GO
IF OBJECT_ID(N'[dbo].[FK_AS_Gallery_Picture]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Pictures] DROP CONSTRAINT [FK_AS_Gallery_Picture];
GO
IF OBJECT_ID(N'[dbo].[FK_AS_User_Event]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Events] DROP CONSTRAINT [FK_AS_User_Event];
GO
IF OBJECT_ID(N'[dbo].[FK_AS_User_Article]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Articles] DROP CONSTRAINT [FK_AS_User_Article];
GO
IF OBJECT_ID(N'[dbo].[FK_AS_Tag_Event_Tag]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AS_Tag_Event] DROP CONSTRAINT [FK_AS_Tag_Event_Tag];
GO
IF OBJECT_ID(N'[dbo].[FK_AS_Tag_Event_Event]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AS_Tag_Event] DROP CONSTRAINT [FK_AS_Tag_Event_Event];
GO
IF OBJECT_ID(N'[dbo].[FK_AS_Tag_Article_Tag]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AS_Tag_Article] DROP CONSTRAINT [FK_AS_Tag_Article_Tag];
GO
IF OBJECT_ID(N'[dbo].[FK_AS_Tag_Article_Article]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AS_Tag_Article] DROP CONSTRAINT [FK_AS_Tag_Article_Article];
GO
IF OBJECT_ID(N'[dbo].[FK_AS_ContentSection_SubSection]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[SubSections] DROP CONSTRAINT [FK_AS_ContentSection_SubSection];
GO
IF OBJECT_ID(N'[dbo].[FK_AS_SubSection_Article_SubSection]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AS_SubSection_Article] DROP CONSTRAINT [FK_AS_SubSection_Article_SubSection];
GO
IF OBJECT_ID(N'[dbo].[FK_AS_SubSection_Article_Article]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AS_SubSection_Article] DROP CONSTRAINT [FK_AS_SubSection_Article_Article];
GO
IF OBJECT_ID(N'[dbo].[FK_AS_Event_Gallery]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Galleries] DROP CONSTRAINT [FK_AS_Event_Gallery];
GO
IF OBJECT_ID(N'[dbo].[FK_AS_Article_Gallery]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Galleries] DROP CONSTRAINT [FK_AS_Article_Gallery];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO
IF OBJECT_ID(N'[dbo].[PasswordResetTokens]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PasswordResetTokens];
GO
IF OBJECT_ID(N'[dbo].[Articles]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Articles];
GO
IF OBJECT_ID(N'[dbo].[Tags]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tags];
GO
IF OBJECT_ID(N'[dbo].[Events]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Events];
GO
IF OBJECT_ID(N'[dbo].[Pictures]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Pictures];
GO
IF OBJECT_ID(N'[dbo].[Galleries]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Galleries];
GO
IF OBJECT_ID(N'[dbo].[ContentSections]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ContentSections];
GO
IF OBJECT_ID(N'[dbo].[SubSections]', 'U') IS NOT NULL
    DROP TABLE [dbo].[SubSections];
GO
IF OBJECT_ID(N'[dbo].[AS_Tag_Event]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AS_Tag_Event];
GO
IF OBJECT_ID(N'[dbo].[AS_Tag_Article]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AS_Tag_Article];
GO
IF OBJECT_ID(N'[dbo].[AS_SubSection_Article]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AS_SubSection_Article];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Username] nvarchar(max)  NOT NULL,
    [Password] nvarchar(max)  NOT NULL,
    [FirstName] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [ActingRole] int  NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [IsHidden] bit  NOT NULL,
    [LastName] nvarchar(max)  NOT NULL,
    [FacebookLink] nvarchar(max)  NOT NULL,
    [TwitterLink] nvarchar(max)  NOT NULL,
    [LinkedInLink] nvarchar(max)  NOT NULL,
    [OtherLink] nvarchar(max)  NOT NULL,
    [ProfilePictureUrl] nvarchar(max)  NOT NULL,
    [UserRightsCsv] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'PasswordResetTokens'
CREATE TABLE [dbo].[PasswordResetTokens] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [TokenValue] nvarchar(max)  NOT NULL,
    [CreatedOn] datetimeoffset  NOT NULL,
    [UsedOn] datetimeoffset  NULL,
    [UserId] int  NOT NULL
);
GO

-- Creating table 'Articles'
CREATE TABLE [dbo].[Articles] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [ShortText] nvarchar(max)  NOT NULL,
    [LongText] nvarchar(max)  NOT NULL,
    [IsHidden] bit  NOT NULL,
    [CreatedOn] datetimeoffset  NOT NULL,
    [IsHot] bit  NOT NULL,
    [UserId] int  NOT NULL,
    [PictureUrlBig] nvarchar(max)  NOT NULL,
    [PictueUrlNormal] nvarchar(max)  NOT NULL,
    [PictureUrlThumbnail] nvarchar(max)  NOT NULL,
    [PublishedOn] datetimeoffset  NULL
);
GO

-- Creating table 'Tags'
CREATE TABLE [dbo].[Tags] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Events'
CREATE TABLE [dbo].[Events] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [ShortText] nvarchar(max)  NOT NULL,
    [LongText] nvarchar(max)  NOT NULL,
    [IsHidden] bit  NOT NULL,
    [CreatedOn] datetimeoffset  NOT NULL,
    [StartsOn] datetimeoffset  NOT NULL,
    [EndsOn] datetimeoffset  NOT NULL,
    [IsHot] bit  NOT NULL,
    [Location] nvarchar(max)  NOT NULL,
    [UserId] int  NOT NULL,
    [PictureUrlBig] nvarchar(max)  NOT NULL,
    [PictureUrlThumbnail] nvarchar(max)  NOT NULL,
    [PictueUrlNormal] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Pictures'
CREATE TABLE [dbo].[Pictures] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Url] nvarchar(max)  NOT NULL,
    [Caption] nvarchar(max)  NOT NULL,
    [GalleryId] int  NOT NULL,
    [IsHidden] bit  NOT NULL
);
GO

-- Creating table 'Galleries'
CREATE TABLE [dbo].[Galleries] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [IsHidden] bit  NOT NULL,
    [Event_Id] int  NULL,
    [Article_Id] int  NULL
);
GO

-- Creating table 'ContentSections'
CREATE TABLE [dbo].[ContentSections] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'SubSections'
CREATE TABLE [dbo].[SubSections] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ContentSectionId] int  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [ThumbnailUrl] nvarchar(max)  NOT NULL,
    [IsHidden] bit  NOT NULL
);
GO

-- Creating table 'AS_Tag_Event'
CREATE TABLE [dbo].[AS_Tag_Event] (
    [Tags_Id] int  NOT NULL,
    [Events_Id] int  NOT NULL
);
GO

-- Creating table 'AS_Tag_Article'
CREATE TABLE [dbo].[AS_Tag_Article] (
    [Tags_Id] int  NOT NULL,
    [Articles_Id] int  NOT NULL
);
GO

-- Creating table 'AS_SubSection_Article'
CREATE TABLE [dbo].[AS_SubSection_Article] (
    [SubSections_Id] int  NOT NULL,
    [Articles_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'PasswordResetTokens'
ALTER TABLE [dbo].[PasswordResetTokens]
ADD CONSTRAINT [PK_PasswordResetTokens]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Articles'
ALTER TABLE [dbo].[Articles]
ADD CONSTRAINT [PK_Articles]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Tags'
ALTER TABLE [dbo].[Tags]
ADD CONSTRAINT [PK_Tags]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Events'
ALTER TABLE [dbo].[Events]
ADD CONSTRAINT [PK_Events]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Pictures'
ALTER TABLE [dbo].[Pictures]
ADD CONSTRAINT [PK_Pictures]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Galleries'
ALTER TABLE [dbo].[Galleries]
ADD CONSTRAINT [PK_Galleries]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ContentSections'
ALTER TABLE [dbo].[ContentSections]
ADD CONSTRAINT [PK_ContentSections]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'SubSections'
ALTER TABLE [dbo].[SubSections]
ADD CONSTRAINT [PK_SubSections]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Tags_Id], [Events_Id] in table 'AS_Tag_Event'
ALTER TABLE [dbo].[AS_Tag_Event]
ADD CONSTRAINT [PK_AS_Tag_Event]
    PRIMARY KEY NONCLUSTERED ([Tags_Id], [Events_Id] ASC);
GO

-- Creating primary key on [Tags_Id], [Articles_Id] in table 'AS_Tag_Article'
ALTER TABLE [dbo].[AS_Tag_Article]
ADD CONSTRAINT [PK_AS_Tag_Article]
    PRIMARY KEY NONCLUSTERED ([Tags_Id], [Articles_Id] ASC);
GO

-- Creating primary key on [SubSections_Id], [Articles_Id] in table 'AS_SubSection_Article'
ALTER TABLE [dbo].[AS_SubSection_Article]
ADD CONSTRAINT [PK_AS_SubSection_Article]
    PRIMARY KEY NONCLUSTERED ([SubSections_Id], [Articles_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [UserId] in table 'PasswordResetTokens'
ALTER TABLE [dbo].[PasswordResetTokens]
ADD CONSTRAINT [FK_AS_User_PasswordResetToken]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AS_User_PasswordResetToken'
CREATE INDEX [IX_FK_AS_User_PasswordResetToken]
ON [dbo].[PasswordResetTokens]
    ([UserId]);
GO

-- Creating foreign key on [GalleryId] in table 'Pictures'
ALTER TABLE [dbo].[Pictures]
ADD CONSTRAINT [FK_AS_Gallery_Picture]
    FOREIGN KEY ([GalleryId])
    REFERENCES [dbo].[Galleries]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AS_Gallery_Picture'
CREATE INDEX [IX_FK_AS_Gallery_Picture]
ON [dbo].[Pictures]
    ([GalleryId]);
GO

-- Creating foreign key on [UserId] in table 'Events'
ALTER TABLE [dbo].[Events]
ADD CONSTRAINT [FK_AS_User_Event]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AS_User_Event'
CREATE INDEX [IX_FK_AS_User_Event]
ON [dbo].[Events]
    ([UserId]);
GO

-- Creating foreign key on [UserId] in table 'Articles'
ALTER TABLE [dbo].[Articles]
ADD CONSTRAINT [FK_AS_User_Article]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AS_User_Article'
CREATE INDEX [IX_FK_AS_User_Article]
ON [dbo].[Articles]
    ([UserId]);
GO

-- Creating foreign key on [Tags_Id] in table 'AS_Tag_Event'
ALTER TABLE [dbo].[AS_Tag_Event]
ADD CONSTRAINT [FK_AS_Tag_Event_Tag]
    FOREIGN KEY ([Tags_Id])
    REFERENCES [dbo].[Tags]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Events_Id] in table 'AS_Tag_Event'
ALTER TABLE [dbo].[AS_Tag_Event]
ADD CONSTRAINT [FK_AS_Tag_Event_Event]
    FOREIGN KEY ([Events_Id])
    REFERENCES [dbo].[Events]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AS_Tag_Event_Event'
CREATE INDEX [IX_FK_AS_Tag_Event_Event]
ON [dbo].[AS_Tag_Event]
    ([Events_Id]);
GO

-- Creating foreign key on [Tags_Id] in table 'AS_Tag_Article'
ALTER TABLE [dbo].[AS_Tag_Article]
ADD CONSTRAINT [FK_AS_Tag_Article_Tag]
    FOREIGN KEY ([Tags_Id])
    REFERENCES [dbo].[Tags]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Articles_Id] in table 'AS_Tag_Article'
ALTER TABLE [dbo].[AS_Tag_Article]
ADD CONSTRAINT [FK_AS_Tag_Article_Article]
    FOREIGN KEY ([Articles_Id])
    REFERENCES [dbo].[Articles]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AS_Tag_Article_Article'
CREATE INDEX [IX_FK_AS_Tag_Article_Article]
ON [dbo].[AS_Tag_Article]
    ([Articles_Id]);
GO

-- Creating foreign key on [ContentSectionId] in table 'SubSections'
ALTER TABLE [dbo].[SubSections]
ADD CONSTRAINT [FK_AS_ContentSection_SubSection]
    FOREIGN KEY ([ContentSectionId])
    REFERENCES [dbo].[ContentSections]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AS_ContentSection_SubSection'
CREATE INDEX [IX_FK_AS_ContentSection_SubSection]
ON [dbo].[SubSections]
    ([ContentSectionId]);
GO

-- Creating foreign key on [SubSections_Id] in table 'AS_SubSection_Article'
ALTER TABLE [dbo].[AS_SubSection_Article]
ADD CONSTRAINT [FK_AS_SubSection_Article_SubSection]
    FOREIGN KEY ([SubSections_Id])
    REFERENCES [dbo].[SubSections]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Articles_Id] in table 'AS_SubSection_Article'
ALTER TABLE [dbo].[AS_SubSection_Article]
ADD CONSTRAINT [FK_AS_SubSection_Article_Article]
    FOREIGN KEY ([Articles_Id])
    REFERENCES [dbo].[Articles]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AS_SubSection_Article_Article'
CREATE INDEX [IX_FK_AS_SubSection_Article_Article]
ON [dbo].[AS_SubSection_Article]
    ([Articles_Id]);
GO

-- Creating foreign key on [Event_Id] in table 'Galleries'
ALTER TABLE [dbo].[Galleries]
ADD CONSTRAINT [FK_AS_Event_Gallery]
    FOREIGN KEY ([Event_Id])
    REFERENCES [dbo].[Events]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AS_Event_Gallery'
CREATE INDEX [IX_FK_AS_Event_Gallery]
ON [dbo].[Galleries]
    ([Event_Id]);
GO

-- Creating foreign key on [Article_Id] in table 'Galleries'
ALTER TABLE [dbo].[Galleries]
ADD CONSTRAINT [FK_AS_Article_Gallery]
    FOREIGN KEY ([Article_Id])
    REFERENCES [dbo].[Articles]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AS_Article_Gallery'
CREATE INDEX [IX_FK_AS_Article_Gallery]
ON [dbo].[Galleries]
    ([Article_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------